<cfsetting enablecfoutputonly="true" showdebugoutput="false">

<cfset Request.PageTitle="NaNoWriMo Progress Graph Statistics">
<cfset Request.IncludeYUI=true>
<cfinclude template="_header.cfm">

<cfquery datasource="rickosborne" name="StatusPie" cachedwithin="#CreateTimeSpan(0,4,0,0)#">
SELECT status, COUNT(*) AS HowMany
FROM nwlogs
WHERE ([format] IS NOT NULL)
  AND (dtday >= {d '2006-11-01'})
GROUP BY status
ORDER BY status
</cfquery>

<cfquery datasource="rickosborne" name="StatusByDay" cachedwithin="#CreateTimeSpan(0,4,0,0)#">
SELECT
	<cfloop query="StatusPie">
  SUM(CASE WHEN l.Status = #Status# THEN 1 ELSE 0 END) AS S#Status#,
	</cfloop>
  DAY(p.When_Date) AS Dom
FROM PivotDays AS p LEFT OUTER JOIN nwlogs AS l ON (l.[format] IS NOT NULL) AND (p.When_Date = l.dtday)
WHERE (p.When_Date BETWEEN {d '2006-11-01'} AND GETDATE())
GROUP BY p.When_Date
ORDER BY p.When_Date
</cfquery>

<cfquery datasource="rickosborne" name="FormatPie" cachedwithin="#CreateTimeSpan(0,4,0,0)#">
SELECT [format], COUNT(*) AS HowMany
FROM nwlogs
WHERE ([format] IS NOT NULL)
  AND (status IN (200,304))
  AND (dtday >= {d '2006-11-01'})
GROUP BY [format]
ORDER BY [format]
</cfquery>

<cfquery datasource="rickosborne" name="FormatWriters" cachedwithin="#CreateTimeSpan(0,4,0,0)#">
SELECT [format], COUNT(*) AS HowMany
FROM (
    SELECT [format], uid
    FROM nwlogs
    WHERE ([format] IS NOT NULL)
      AND (status IN (200,304))
      AND (dtday >= {d '2006-11-01'})
    GROUP BY [format], uid
    HAVING COUNT(*) > 8
  ) AS f
GROUP BY [format]
ORDER BY [format]
</cfquery>

<cfquery datasource="rickosborne" name="FormatByDay" cachedwithin="#CreateTimeSpan(0,4,0,0)#">
SELECT
	<cfloop query="FormatPie">
  SUM(CASE WHEN l.[format] = '#Format#' THEN 1 ELSE 0 END) AS #Format#,
	</cfloop>
  DAY(p.When_Date) AS Dom
FROM PivotDays AS p LEFT OUTER JOIN nwlogs AS l ON (l.status IN (200,304)) AND (l.[format] IS NOT NULL) AND (p.When_Date = l.dtday)
WHERE (p.When_Date BETWEEN {d '2006-11-01'} AND GETDATE())
GROUP BY p.When_Date
ORDER BY p.When_Date
</cfquery>

<cfquery datasource="rickosborne" name="FormatViewersByDay" cachedwithin="#CreateTimeSpan(0,4,0,0)#">
SELECT
	<cfloop query="FormatPie">
  SUM(CASE WHEN l.[format] = '#Format#' THEN viewers ELSE 0 END) AS #Format#,
	</cfloop>
  DAY(p.When_Date) AS Dom
FROM PivotDays AS p LEFT OUTER JOIN (
    SELECT dtday, [format], COUNT(DISTINCT ip) AS viewers
    FROM nwlogs AS l
    WHERE (l.status IN (200,304))
      AND (l.[format] IS NOT NULL)
      AND (l.dtday BETWEEN {d '2006-11-01'} AND GETDATE())
    GROUP BY dtday, [format]
  ) AS l ON (p.When_Date = l.dtday)
WHERE (p.When_Date BETWEEN {d '2006-11-01'} AND GETDATE())
GROUP BY p.When_Date
ORDER BY p.When_Date
</cfquery>

<cfquery datasource="rickosborne" name="FormatWritersByDay" cachedwithin="#CreateTimeSpan(0,4,0,0)#">
SELECT
	<cfloop query="FormatPie">
  SUM(CASE WHEN l.[format] = '#Format#' THEN writers ELSE 0 END) AS #Format#,
	</cfloop>
  DAY(p.When_Date) AS Dom
FROM PivotDays AS p LEFT OUTER JOIN (
    SELECT dtday, [format], COUNT(DISTINCT uid) AS writers
    FROM nwlogs AS l
    WHERE (l.status IN (200,304))
      AND (l.[format] IS NOT NULL)
      AND (l.dtday BETWEEN {d '2006-11-01'} AND GETDATE())
    GROUP BY dtday, [format]
  ) AS l ON (p.When_Date = l.dtday)
WHERE (p.When_Date BETWEEN {d '2006-11-01'} AND GETDATE())
GROUP BY p.When_Date
ORDER BY p.When_Date
</cfquery>

<cfquery datasource="rickosborne" name="FormatKbpsByDay" cachedwithin="#CreateTimeSpan(0,4,0,0)#">
SELECT
	<cfloop query="FormatPie">
  SUM(CASE WHEN l.[format] = '#Format#' THEN CONVERT(MONEY,bytes) / 11059200 ELSE 0 END) AS #Format#,
	</cfloop>
  DAY(p.When_Date) AS Dom
FROM PivotDays AS p LEFT OUTER JOIN nwlogs AS l ON (l.status IN (200,304)) AND (l.[format] IS NOT NULL) AND (p.When_Date = l.dtday)
WHERE (p.When_Date BETWEEN {d '2006-11-01'} AND GETDATE())
GROUP BY p.When_Date
ORDER BY p.When_Date
</cfquery>

<cfquery datasource="rickosborne" name="FormatBytes" cachedwithin="#CreateTimeSpan(0,4,0,0)#">
SELECT [format], SUM(CONVERT(MONEY,bytes)) / 1048576 AS HowMany
FROM nwlogs
WHERE ([format] IS NOT NULL)
  AND (status IN (200,304))
  AND (dtday >= {d '2006-11-01'})
GROUP BY [format]
ORDER BY [format]
</cfquery>

<cfquery datasource="rickosborne" name="FormatHourBytes" cachedwithin="#CreateTimeSpan(0,4,0,0)#">
SELECT
	<cfloop query="FormatPie">
  SUM(CASE WHEN [format] = '#Format#' THEN CONVERT(MONEY,bytes) ELSE 0 END) / (460800 * COUNT(DISTINCT dtday)) AS #Format#,
	</cfloop>
  DATEPART(HOUR,dt) AS "Hour"
FROM nwlogs
WHERE ([format] IS NOT NULL)
  AND (status = 200)
  AND (dtday >= {d '2006-11-01'})
GROUP BY DATEPART(HOUR,dt)
ORDER BY DATEPART(HOUR,dt)
</cfquery>

<cfset StatusColors="##ccffcc,##ccccff,##ffcccc,##ffdddd,##ffeeee,##ffbbbb,##ffaaaa">
<cfset FormatColors="##ccccff,##ffcccc,##dddddd,##ccffcc">

<cfoutput>
<h2 id="statstitle">#Request.PageTitle#</h2>

<div id="stats" class="yui-navset">
	<div id="worldmap" class="mod">
		<div class="hd"><h3>Viewers By Country</h3></div>
		<div class="bd">
		<img src="http://nowrists.com/worldmap.cfm//m.png" width="615" height="393" border="0" alt="NoWriSts.com Graph Viewers By Country" />
		<p>This map shows where the viewers (not writers) of the graphs are coming from.  Darker colors represent more viewers, while lighter colors represent fewer viewers.  Countries with no viewers are shaded a neutral grey.</p>
		</div>
	</div>
	<div id="formatwriters" class="mod">
		<div class="hd"><h3>Format Writers</h3></div>
		<div class="bd">
		<cfchart chartheight="300" chartwidth="400" format="png" show3d="true">
			<cfchartseries type="pie" colorlist="#FormatColors#" itemcolumn="format" query="FormatWriters" valuecolumn="HowMany" datalabelstyle="none">
		</cfchart>
		<p>This chart represents the relative popularity of the graph formats.  It is a count of unique NaNoWriMo IDs filtered a bit to exclude the hits generated by the initial setup.</p>
		</div>
	</div>
	<div id="hitsbyformat" class="mod">
		<div class="hd"><h3>Hits By Format</h3></div>
		<div class="bd">
		<cfchart chartheight="300" chartwidth="400" format="png" show3d="true">
			<cfchartseries type="pie" colorlist="#FormatColors#" itemcolumn="format" query="FormatPie" valuecolumn="HowMany" datalabelstyle="none">
		</cfchart>
		<p>This chart shows total traffic (hits) per graph format.</p>
		</div>
	</div>
	<div id="formatbyday" class="mod">
		<div class="hd"><h3>Format Hits By Day</h3></div>
		<div class="bd">
		<cfchart chartheight="300" chartwidth="400" format="png" show3d="false" seriesplacement="stacked" sortxaxis="false" >
			<cfloop from="#FormatPie.RecordCount#" to="1" step="-1" index="i">
				<cfchartseries type="area" itemcolumn="Dom" query="FormatByDay" valuecolumn="#FormatPie.Format[i]#" seriescolor="#ListGetAt(FormatColors,i)#" serieslabel="#FormatPie.Format[i]#">
			</cfloop>
		</cfchart>
		<p>This chart shows how total traffic for each graph format has changed over time.</p>
		</div>
	</div>
	<div id="formatviewersbyday" class="mod">
		<div class="hd"><h3>Format Viewers By Day</h3></div>
		<div class="bd">
		<cfchart chartheight="300" chartwidth="400" format="png" show3d="false" seriesplacement="stacked" sortxaxis="false" >
			<cfloop from="#FormatPie.RecordCount#" to="1" step="-1" index="i">
				<cfchartseries type="area" itemcolumn="Dom" query="FormatViewersByDay" valuecolumn="#FormatPie.Format[i]#" seriescolor="#ListGetAt(FormatColors,i)#" serieslabel="#FormatPie.Format[i]#">
			</cfloop>
		</cfchart>
		<p>This chart shows how viewer (not writer) traffic for each graph format has changed over time.</p>
		</div>
	</div>
	<div id="formatwritersbyday" class="mod">
		<div class="hd"><h3>Format Writers By Day</h3></div>
		<div class="bd">
		<cfchart chartheight="300" chartwidth="400" format="png" show3d="false" seriesplacement="stacked" sortxaxis="false" >
			<cfloop from="#FormatPie.RecordCount#" to="1" step="-1" index="i">
				<cfchartseries type="area" itemcolumn="Dom" query="FormatWritersByDay" valuecolumn="#FormatPie.Format[i]#" seriescolor="#ListGetAt(FormatColors,i)#" serieslabel="#FormatPie.Format[i]#">
			</cfloop>
		</cfchart>
		<p>This chart shows how many writers are using each graph format over time.</p>
		</div>
	</div>
	<div id="formatbytes" class="mod">
		<div class="hd"><h3>Format Bytes Downloaded (MB)</h3></div>
		<div class="bd">
		<cfchart chartheight="300" chartwidth="400" format="png" show3d="true">
			<cfchartseries type="pie" colorlist="#FormatColors#" itemcolumn="format" query="FormatBytes" valuecolumn="HowMany" datalabelstyle="none">
		</cfchart>
		<p>This chart shows the total megabytes (MB) downloaded per graph format.</p>
		</div>
	</div>
	<div id="formathourlybandwidth" class="mod">
		<div class="hd"><h3>Format Hourly Bandwidth (kbps)</h3></div>
		<div class="bd">
		<cfchart chartheight="300" chartwidth="400" format="png" show3d="false" seriesplacement="stacked" sortxaxis="false" >
			<cfloop from="#FormatPie.RecordCount#" to="1" step="-1" index="i">
				<cfchartseries type="area" itemcolumn="Hour" query="FormatHourBytes" valuecolumn="#FormatPie.Format[i]#" seriescolor="#ListGetAt(FormatColors,i)#" serieslabel="#FormatPie.Format[i]#">
			</cfloop>
		</cfchart>
		<p>This chart shows average bandwidth requirements in kilobytes per second (kbps) for each graph by hour of the day.  It can be interpreted as an indicator of server load over the course of an average day.</p>
		</div>
	</div>
	<div id="formatdailybandwidth" class="mod">
		<div class="hd"><h3>Format Daily Bandwidth (kbps)</h3></div>
		<div class="bd">
		<cfchart chartheight="300" chartwidth="400" format="png" show3d="false" seriesplacement="stacked" sortxaxis="false" >
			<cfloop from="#FormatPie.RecordCount#" to="1" step="-1" index="i">
				<cfchartseries type="area" itemcolumn="Dom" query="FormatKbpsByDay" valuecolumn="#FormatPie.Format[i]#" seriescolor="#ListGetAt(FormatColors,i)#" serieslabel="#FormatPie.Format[i]#">
			</cfloop>
		</cfchart>
		<p>This chart shows bandwidth requirements in kilobytes per second (kbps) for each graph format by day.</p>
		</div>
	</div>
	<div id="statuscodes" class="mod">
		<div class="hd"><h3>Hits By Status Code</h3></div>
		<div class="bd">
		<cfchart chartheight="300" chartwidth="400" format="png" show3d="true">
			<cfchartseries type="pie" colorlist="#StatusColors#" itemcolumn="status" query="StatusPie" valuecolumn="HowMany" datalabelstyle="none">
		</cfchart>
		<p>This chart shows HTTP status codes returned by the graph generator.  The 200 status means <em>&ldquo;OK&rdquo;</em>, and that a full image was sent out with the request.  The 304 status means that the graph had been unchanged since the last time that viewer saw it, so no new image was generated.  (This saves bandwidth!)  The 404 status is used when a viewer requested something they shouldn't have.  The 500-series status represent server errors, and normally mean things like invalid Unicode encoding or something else bad.</p>
		</div>
	</div>
	<div id="statusbyday" class="mod">
		<div class="hd"><h3>Status Codes By Day</h3></div>
		<div class="bd">
		<cfchart chartheight="300" chartwidth="400" format="png" show3d="false" seriesplacement="stacked" sortxaxis="false" >
			<cfloop from="#StatusPie.RecordCount#" to="1" step="-1" index="i">
				<cfchartseries type="area" itemcolumn="Dom" query="StatusByDay" valuecolumn="S#StatusPie.Status[i]#" seriescolor="#ListGetAt(StatusColors,i)#" serieslabel="Status #StatusPie.Status[i]#">
			</cfloop>
		</cfchart>
		<p>This chart shows how HTTP status codes have changed over time.  In theory, non-200 and non-304 status codes should decrease over time, as more bugs are ironed out of the system.</p>
		</div>
	</div>
</div>
<script language="javascript" type="text/javascript">
var tabView = new YAHOO.widget.TabView();
YAHOO.util.Event.onContentReady('stats',function() {
	tabView.set('orientation','left');
	YAHOO.util.Dom.batch(YAHOO.util.Dom.getElementsByClassName('mod','div',this),function(module) {
		tabView.addTab(new YAHOO.widget.Tab({
			label: module.getElementsByTagName('h3')[0].innerHTML,
			contentEl: YAHOO.util.Dom.getElementsByClassName('bd','div',module)[0]
		}));
		YAHOO.util.Dom.setStyle(module,'display','none');
	});
	tabView.set('activeIndex',0);
	tabView.appendTo(this);
});
</script>
<style type="text/css">
##stats .yui-content {
    padding:1em;
}
##stats .yui-nav { width: 12em; margin-left: -7em; background-color: ##f0f0f0; }
##stats { margin-left: 7em; }
.yui-navset .yui-nav a { border-bottom: 3px solid transparent; font-size: 0.75em; }
.yui-navset .yui-nav .selected a, .yui-navset .yui-nav a:hover {
    background-color:##ddd;
	border-bottom: 3px solid ##dc9;
}
.yui-nav { border: 1px solid ##aaa; }
</style>
</cfoutput>

<cfinclude template="_footer.cfm">

<cfsetting enablecfoutputonly="false">